package org.example.springboot.mapper;

import org.apache.ibatis.annotations.*;
import org.example.springboot.entity.Teach;
import org.example.springboot.entity.TeacherAndCourse;

import java.util.List;

@Mapper
public interface TeachMapper {


    @Select("SELECT t.id, t.account, t.course, t.teach_method, t.time, t.teach_situation, t.salary, te.name " +
            "FROM teach t " +
            "JOIN teacher te ON t.account = te.account " +
            "WHERE te.name LIKE CONCAT('%', #{name}, '%') " +
            "AND t.course LIKE CONCAT('%', #{course}, '%') " +
            "LIMIT #{offset}, #{pageSize}")
    List<Teach> findByNameAndCourse(@Param("name") String name,
                                           @Param("course") String course,
                                           @Param("offset") int offset,
                                           @Param("pageSize") int pageSize);


    @Select("SELECT t.id, t.account, t.course, t.teach_method, t.time, t.teach_situation, t.salary, te.name " +
            "FROM teach t " +
            "JOIN teacher te ON t.account = te.account " +
            "WHERE te.name LIKE CONCAT('%', #{name}, '%') " +
            "LIMIT #{offset}, #{pageSize}")
    List<Teach> findByName(@Param("name") String name,
                                  @Param("offset") int offset,
                                  @Param("pageSize") int pageSize);

    @Select("SELECT t.id, t.account, t.course, t.teach_method, t.time, t.teach_situation, t.salary, te.name " +
            "FROM teach t " +
            "JOIN teacher te ON t.account = te.account " +
            "WHERE t.course LIKE CONCAT('%', #{course}, '%') " +
            "LIMIT #{offset}, #{pageSize}")
    List<Teach> findByCourse(@Param("course") String course,
                                @Param("offset") int offset,
                                @Param("pageSize") int pageSize);


    @Select("SELECT COUNT(*) " +
            "FROM teach t " +
            "JOIN teacher te ON t.account = te.account " +
            "WHERE te.username LIKE CONCAT('%', #{name}, '%') " +
            "AND t.course LIKE CONCAT('%', #{course}, '%')")
    int countByNameAndCourse(@Param("name") String name, @Param("course") String course);


    @Select("SELECT COUNT(*) " +
            "FROM teach t " +
            "JOIN teacher te ON t.account = te.account " +
            "WHERE te.name LIKE CONCAT('%', #{name}, '%')")
    int countByName(@Param("name") String name);


    @Select("SELECT COUNT(*) " +
            "FROM teach t " +
            "JOIN teacher te ON t.account = te.account " +
            "WHERE t.course LIKE CONCAT('%', #{course}, '%')")
    int countByCourse(@Param("course") String course);

    @Select("SELECT * FROM teach JOIN teacher ON teach.account = teacher.account")
    List<TeacherAndCourse> findAll();

    @Select("SELECT * FROM teach WHERE account = #{account}")
    List<Teach> findByAccount(@Param("account") String account);

    @Insert("INSERT INTO teach (account, course, teach_method, time, teach_situation, salary) VALUES (#{account}, #{course}, #{teach_method}, #{time}, #{teach_situation}, #{salary})")
    void insert(Teach teach);

    @Delete("DELETE FROM teach WHERE id = #{id}")
    void deleteTeach(@Param("id") int id);
}
